﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FremtidensPlejehjem.Database
{
    public static class DbActivity
    {
        public static void CreateActivity(string activityName, string activityDescription, int roomNumber, string activityStartDate, string activityEndDate)
        {
            string query = "insert into Activity (activityName, activityDescription) values (@activityName, @activityDescription) " +
                           "insert into Activity_Room (activityID, roomNumber, startDate, endDate) values(@actvityId, @roomNumber, @activityStartDate, @activityEndDate)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityName", activityName);
                    cmd.Parameters.AddWithValue("@activityDescription", activityDescription);
                    cmd.Parameters.AddWithValue("@activityId", DbGeneral.GetNextId("activityID", "Activity"));
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityStartDate", activityStartDate);
                    cmd.Parameters.AddWithValue("@activityEndDate", activityEndDate);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static /*Activity*/void GetActivityById(int activityId)
        {
            string query = "select ac.activityName, ac.activityDescription, ar.startDate, ar.endDate, r.roomNumber, r.purpose " +
                           "from Activity ac, Room r, Activity_Room ar " +
                           "where ac.activityID = @activityId " +
                           "and ar.activityID = ac.activityID " +
                           "and r.roomNumber = ar.roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    //Activity activity = null

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        //activity = new Activity()
                        
                        //activity.Name = dr["activityName"].ToString();
                        //activity.Description = dr["activityDescription"].ToString();

                        //activity.StartDate = Convert.ToDateTime(dr["startDate"]);
                        //activity.EndDate = Convert.ToDateTime(dr["endDate"]);

                        //activity.Room = new Room(dr["roomNumber"], dr["purpose"]);
                    }

                    conn.Close();

                    //return activity;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static /*List<Activities>*/ void GetActivitiesByRoom(int roomNumber)
        {
            string query = "select ac.activityName, ac.activityDescription, ar.startDate, ar.endDate, r.roomNumber, r.purpose " +
                           "from Activity ac, Room r, Activity_Room ar " +
                           "where r.roomNumber = @roomNumber" +
                           "and ar.roomNumber = r.roomNumber" +
                           "and ac.activityID = ar.activityId ";
                           

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    //Activity activity = null

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        //activity = new Activity()

                        //activity.Name = dr["activityName"].ToString();
                        //activity.Description = dr["activityDescription"].ToString();

                        //activity.StartDate = Convert.ToDateTime(dr["startDate"]);
                        //activity.EndDate = Convert.ToDateTime(dr["endDate"]);

                        //activity.Room = new Room(dr["roomNumber"], dr["purpose"]);
                    }

                    conn.Close();

                    //return activity;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static /*List<Activity>*/ void GetAllActivities()
        {
            string query = "select ac.activityName, ac.activityDescription, ar.startDate, ar.endDate, r.roomNumber, r.purpose " +
                           "from Activity ac, Room r, Activity_Room ar " +
                           "where ar.activityID = ac.activityID " +
                           "and r.roomNumber = ar.roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    //list<Activity> activities = null

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        //Activity activity = new Activity()

                        //activity.Name = dr["activityName"].ToString();
                        //activity.Description = dr["activityDescription"].ToString();

                        //activity.StartDate = Convert.ToDateTime(dr["startDate"]);
                        //activity.EndDate = Convert.ToDateTime(dr["endDate"]);

                        //activity.Room = new Room(dr["roomNumber"], dr["purpose"]);

                        //activies.Add(activity);
                    }

                    conn.Close();

                    //return activities;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static /*List<Activity>*/ void Get5NextActivities()
        {
            string query = "select top 5 ac.activityName, ac.activityDescription, ar.startDate, ar.endDate, r.roomNumber, r.purpose " +
                           "from Activity ac, Room r, Activity_Room ar " +
                           "where ar.activityID = ac.activityID " +
                           "and r.roomNumber = ar.roomNumber " +
                           "where ar.startDate > CURRENT_TIMESTAMP " +
                           "orderby ar.startDate asc";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    //list<Activity> activities = null

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        //Activity activity = new Activity()

                        //activity.Name = dr["activityName"].ToString();
                        //activity.Description = dr["activityDescription"].ToString();

                        //activity.StartDate = Convert.ToDateTime(dr["startDate"]);
                        //activity.EndDate = Convert.ToDateTime(dr["endDate"]);

                        //activity.Room = new Room(dr["roomNumber"], dr["purpose"]);

                        //activies.Add(activity);
                    }

                    conn.Close();

                    //return activities;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static void UpdateActivity(int activityId, string activityName, string activityDescription)
        {
            string query = "update Activity set activityName = @activityName, activityDescription = @activityDescription where activityID = @activityId ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@activityName", activityName);
                    cmd.Parameters.AddWithValue("@activityDescription", activityDescription);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static void UpdateActivityRoom(int activityId, int roomNumber, string activityStartDate, string activityEndDate)
        {
            string query = "update Activity_Room set roomNumber = @roomNumber, activityStartDate = @activityStartDate, activityEndDate = @activityEndDate where activityID = @activityId ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityStartDate", activityStartDate);
                    cmd.Parameters.AddWithValue("@activityEndDate", activityEndDate);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static void InsertAdditionalActivityRoom(int activityId, int roomNumber, string activityStartDate, string activityEndDate)
        {
            string query = "insert into Activity_Room (activityID, roomNumber, startDate, endDate) values(@actvityId, @roomNumber, @activityStartDate, @activityEndDate ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityStartDate", activityStartDate);
                    cmd.Parameters.AddWithValue("@activityEndDate", activityEndDate);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static void DeleteActivity(int activityId)
        {
            string query = "delete from Activity_Room where activityID = @activityId " +
                           "delete from Activity where activityID = @activityId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        public static void RemoveActivityRoomFromActivity(int activityId, int roomNumber)
        {
            string query = "delete from Activity_Room where activityID = @activityId and roomNumber = @roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }
    }
}
